import mysql.connector
from mysql.connector import Error

# 数据库配置信息
config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'swc1234',
    'database': 'aaaa'
}


# 连接数据库
def connect_db():
    return mysql.connector.connect(**config)


# 插入歌曲
def insert_song(sid, length, bpm, title, artist, s_mode, time, s_md5, s_file_path, img_md5, img_file_path):
    try:
        conn = connect_db()
        cursor = conn.cursor()
        query = "INSERT INTO song (sid, length, bpm, title, artist, s_mode, time, s_md5, s_file_path, img_md5, img_file_path) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        cursor.execute(query, (sid, length, bpm, title, artist, s_mode, time, s_md5, s_file_path, img_md5, img_file_path))
        conn.commit()
        print("歌曲插入成功")
    except Error as e:
        print("插入错误：", e)
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()


# 删除歌曲
def delete_song(title):
    try:
        conn = connect_db()
        cursor = conn.cursor()
        query = "DELETE FROM song WHERE title = %s"
        cursor.execute(query, (title,))
        conn.commit()
        print("歌曲删除成功")
    except Error as e:
        print("删除错误：", e)
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()


# 查询歌曲
def query_song_by_title(title):
    try:
        conn = connect_db()
        cursor = conn.cursor()
        query = "SELECT * FROM song WHERE title = %s"
        cursor.execute(query, (title,))
        result = cursor.fetchall()
        return result
    except Error as e:
        print("查询错误：", e)
        return None
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

# 插入记录
def insert_chart_record(cid, sid, uid, version, level, type, size, c_mode, c_md5, c_file_path):
    try:
        conn = connect_db()
        if conn is not None:
            cursor = conn.cursor()
            query = """
                INSERT INTO chart (cid, sid, uid, version, level, type, size, c_mode, c_md5, c_file_path)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(query, (cid, sid, uid, version, level, type, size, c_mode, c_md5, c_file_path))
            conn.commit()
            print("Record inserted successfully")
    except Error as e:
        print(f"Error inserting record: {e}")
    finally:
        if conn is not None:
            cursor.close()
            conn.close()

# 删除记录
def delete_chart_record(cid):
    try:
        conn = connect_db()
        if conn is not None:
            cursor = conn.cursor()
            query = "DELETE FROM chart WHERE cid = %s"
            cursor.execute(query, (cid,))
            conn.commit()
            print("Record deleted successfully")
    except Error as e:
        print(f"Error deleting record: {e}")
    finally:
        if conn is not None:
            cursor.close()
            conn.close()

# 查询记录
def query_chart_record_by_cid(cid):
    try:
        conn = connect_db()
        if conn is not None:
            cursor = conn.cursor()
            query = "SELECT * FROM chart WHERE cid = %s"
            cursor.execute(query, (cid,))
            result = cursor.fetchone()
            return result
    except Error as e:
        print(f"Error querying record: {e}")
        return None
    finally:
        if conn is not None:
            cursor.close()
            conn.close()


# 示例用法
if __name__ == "__main__":
    # 插入歌曲
    insert_song(1127, 240, 120, 'Song Title', 'Artist Name', 1, 1630000, 'md5value', '/path/to/file', 'imgmd5value',
                '/path/to/image')

    # 删除歌曲
    delete_song('Song Title')

    # 查询歌曲
    songs = query_song_by_title('Song Title')
    print(songs)